package com.motionlog.model;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.os.Environment;

public class MyDatabase extends SQLiteOpenHelper {

	private static final String TAG = MyDatabase.class.getSimpleName();

	public MyDatabase(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	private static MyDatabase sDatabase;

	public static MyDatabase getInstance(Context context) {
		if (sDatabase == null) {
			sDatabase = new MyDatabase(context, "motion.db", null, 11);
		}
		return sDatabase;
	}

	public long insertMotionLike(String table, String name) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		long id = getWritableDatabase().insert(table, null, cv);
		return id;
	}

	public boolean insertMotionLike(String table, String name, SQLiteDatabase db) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		long id = db.insert(table, null, cv);
		return id == -1 ? false : true;
	}

	public Cursor selectMotionLike(String table) {
		return getReadableDatabase().query(table,
				new String[] { "_id", "name", "content" }, null, null, null,
				null, null);
	}

	public List<? extends Base> getMotions(Class clz) {
		List<Base> motions = new ArrayList<Base>();
		Cursor cursor = selectMotionLike(clz.getSimpleName().toLowerCase());
		while (cursor.moveToNext()) {
			Base base = new Base();
			base.id = cursor.getInt(0);
			base.name = cursor.getString(1);
			base.content = cursor.getString(2);
			motions.add(base);
		}

		return motions;
	}

	public long insertLog(int motionId, int level, String reasonSummary) {
		ContentValues cv = new ContentValues();
		cv.put("motion_id", motionId);
		cv.put("level", level);
		cv.put("reason_summary", reasonSummary);
		long id = getWritableDatabase().insert("log", null, cv);
		return id;
	}

	public int updateLog(long logId, String description, String markup,
			String methodSummary) {
		ContentValues cv = new ContentValues();
		cv.put("motion_reason_description", description);
		cv.put("method_markup", markup);
		cv.put("method_summary", methodSummary);
		int rowEffected = getWritableDatabase().update("log", cv, "_id = ?",
				new String[] { String.valueOf(logId) });
		return rowEffected;
	}

	public int updateLog(long logId, String nowThink, String categorySummary) {
		ContentValues cv = new ContentValues();
		cv.put("now_think", nowThink);
		cv.put("category_summary", categorySummary);
		int rowEffected = getWritableDatabase().update("log", cv, "_id = ?",
				new String[] { String.valueOf(logId) });
		return rowEffected;
	}

	/**
	 * 
	 * @param logId
	 * @param setp
	 *            1 应对方法 2 分类 3 未解决
	 * @return
	 */
	public int updateLogFinishStep(long logId, int step) {
		ContentValues cv = new ContentValues();
		cv.put("finish_step", step);
		int rowEffected = getWritableDatabase().update("log", cv, "_id = ?",
				new String[] { String.valueOf(logId) });
		return rowEffected;
	}

	public long insertReasonLog(long logId, int reasonId) {
		ContentValues cv = new ContentValues();
		cv.put("log_id", logId);
		cv.put("reason_id", reasonId);
		return getWritableDatabase().insert("log_reason", null, cv);
	}

	public long insertMethodLog(long logId, int methodId) {
		ContentValues cv = new ContentValues();
		cv.put("log_id", logId);
		cv.put("method_id", methodId);
		return getWritableDatabase().insert("log_method", null, cv);
	}

	public long insertCategoryLog(long logId, int categoryId) {
		ContentValues cv = new ContentValues();
		cv.put("log_id", logId);
		cv.put("category_id", categoryId);
		return getWritableDatabase().insert("log_category", null, cv);
	}

	private String[] getBeforeWeekDateString(int week) {
		Date now = new Date();
		android.util.Log.d(TAG, "week: " + week);
		long ms = now.getTime() - (long) week * 7 * 24 * 60l * 60l * 1000l;
		android.util.Log.d(TAG,
				"now: " + now + "ms: " + ms + "getTime: " + now.getTime());
		now.setTime(ms);
		android.util.Log.d(TAG,
				"now: " + now + "ms: " + ms + "getTime: " + now.getTime());
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String d = format.format(now);
		android.util.Log.d(TAG, "d:  " + d);
		return new String[] { d };
	}

	public int getLogCount(int week) {
		String sql = "select count(*) as count from log where date(time) >= ?";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getInt(0);
		}
		c.close();

		return 0;
	}

	public String[] getMotionStatistic(int week) {
		String sql = "select motion_id, motion.name as motion_name, date(time) as date, "
				+ "count(motion_id) from log left outer join motion on (motion._id = log.motion_id) "
				+ "where date(time) >= ? group by motion_id order by count(motion_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		android.util.Log.d(TAG, "cursor.coutn: " + c.getCount());
		String[] motions = new String[2];
		for (int i = 0; i < motions.length; i++) {
			if (c.moveToNext()) {
				motions[i] = c.getString(1);
				android.util.Log.d(TAG, "m: " + motions[i]);
			} else {
				motions[i] = "";
			}
		}
		c.close();
		return motions;
	}

	public String getMainReason(int week) {
		String sql = "select reason.name from log_reason left outer join reason on "
				+ "(reason._id = log_reason.reason_id) where date(time) >= ? "
				+ "group by reason_id order by count(reason_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getString(0);
		}
		c.close();
		return "";
	}

	public String getMainMethod(int week) {
		String sql = "select method.name from log_method left outer join method on "
				+ "(method._id = log_method.method_id) where date(time) >= ? "
				+ "group by method_id order by count(method_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getString(0);
		}
		c.close();
		return "";
	}

	public String getMainCategory(int week) {
		String sql = "select category.name from log_category left outer join category on "
				+ "(category._id = log_category.category_id) where date(time) >= ? "
				+ "group by category_id order by count(category_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getString(0);
		}
		c.close();
		return "";
	}

	public Cursor getFinishMethodCount(int week) {
		String sql = "select finish_step, count(finish_step) from log "
				+ "where date(time) >= ? group by finish_step";

		return getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
	}

	public String getReasonSummary(long logId) {
		String sql = "select name from log_reason left outer join reason on "
				+ "(log_reason.reason_id = reason._id) where log_reason.log_id = ?";

		Cursor c = getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(logId) });
		StringBuilder sb = new StringBuilder();
		while (c.moveToNext()) {
			sb.append(c.getString(0));
		}

		return sb.toString();
	}

	public Cursor selectLog(long id) {
		String sql = "select name, reason_summary, motion_reason_description,"
				+ "method_summary,method_markup,now_think,category_summary,level "
				+ "from log left outer join motion on "
				+ "(motion._id = log.motion_id) where log._id = ?";
		return getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(id) });
	}

	public Cursor selectLog(Date date) {
		String query = SQLiteQueryBuilder
				.buildQueryString(
						true,
						"log LEFT OUTER JOIN motion ON (motion._id = log.motion_id)",
						new String[] { "date(time) as date, time(time) as time, "
								+ "time as datetime, motion.name as motion_name, level, "
								+ "log._id as _id" }, "date(time) = ?", null,
						null, null, null);
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String d = format.format(date);
		android.util.Log.d(TAG, "d: " + d + ", query: " + query);
		// 02-18 10:17:34.742: D/Database(14544): d: 2012-2-18, query: SELECT
		// DISTINCT date(time) as date, time(time) as time, time as datetime,
		// motion.name as motion_name, level, log._id as _id FROM log LEFT OUTER
		// JOIN motion ON (motion._id = log.motion_id) WHERE date(time) = ?

		return getReadableDatabase().rawQuery(query, new String[] { d });
		// SQLiteQueryBuilder s = new SQLiteQueryBuilder();
		// s.settfoo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)")
		// return getReadableDatabase().query("log", null, null, null, null,
		// null,
		// null);
	}

	public Cursor selectSaying() {
		return getReadableDatabase().query("saying", null, null, null, null,
				null, null);
	}

	public Saying getRandomSaying() {
		Cursor cursor = selectSaying();
		Random random = new Random();
		int count = cursor.getCount();
		int id = random.nextInt(count) + 1;
		android.util.Log.d(TAG, "_id: " + id + ", count: " + count);
		cursor.close();
		Cursor c = getReadableDatabase().query("saying", null, "_id = ?",
				new String[] { String.valueOf(id) }, null, null, null);
		Saying saying = new Saying();
		if (c.moveToNext()) {
			saying.id = c.getInt(0);
			saying.author = c.getString(1);
			saying.content = c.getString(2);
		}
		return saying;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// db.execSQL("DROP TABLE IF EXISTS " + "motion.db");

		db.execSQL("DROP TABLE IF EXISTS motion");
		db.execSQL("CREATE TABLE motion ("
				+ "_id INTEGER primary key autoincrement, name TEXT, content TEXT"
				+ ")");

		String[] motions = { "愤怒", "恐惧", "焦虑", "沮丧", "痛苦", "悔恨", "寂寞", "委屈",
				"紧张", "拖延", "嫉妒", "纠结", "厌恶", "烦闷" };
		for (int i = 0; i < motions.length; i++) {
			insertMotionLike("motion", motions[i], db);
		}

		db.execSQL("DROP TABLE IF EXISTS reason");
		db.execSQL("CREATE TABLE reason ("
				+ "_id INTEGER primary key autoincrement, name TEXT, content TEXT"
				+ ")");
		String[] reasons = { "人际关系", "工作压力", "学习压力", "环境适应不良", "个性因素", "经济压力" };
		for (int i = 0; i < reasons.length; i++) {
			insertMotionLike("reason", reasons[i], db);
		}

		db.execSQL("DROP TABLE IF EXISTS method");
		db.execSQL("CREATE TABLE method ("
				+ "_id INTEGER primary key autoincrement, name TEXT, content TEXT"
				+ ")");
		String[] method = { "顺其自然，事情没有你想象的那么糟", "放松！", "我已尽力而为。谋事在人，成事在天。","这不是我的错！","无论发生什么情况，我都能够对付它","这件事不是什么致命的问题，不要把它看得太重要" };
		for (int i = 0; i < method.length; i++) {
			insertMotionLike("method", method[i], db);
		}

		db.execSQL("DROP TABLE IF EXISTS category");
		db.execSQL("CREATE TABLE category ("
				+ "_id INTEGER primary key autoincrement, name TEXT, content TEXT"
				+ ")");
		String[] category = { "主观推断", "选择性概括", "过度概括","夸大与缩小","个性化","贴标签","极端化思维"};
		for (int i = 0; i < category.length; i++) {
			insertMotionLike("category", category[i], db);
		}

		db.execSQL("DROP TABLE IF EXISTS log");
		db.execSQL("CREATE TABLE log ("
				+ "_id INTEGER primary key autoincrement, motion_id INTEGER, "
				+ "level INTEGER, time TIMESTAMP default (datetime('now', 'localtime')), "
				+ "motion_reason_description TEXT, method_markup TEXT, "
				+ "now_think TEXT, finish_step INTEGER, reason_summary TEXT, "
				+ "method_summary TEXT, category_summary TEXT" + ")");

		db.execSQL("DROP TABLE IF EXISTS log_reason");
		db.execSQL("CREATE TABLE log_reason ("
				+ "_id INTEGER primary key autoincrement, reason_id INTEGER, "
				+ "log_id INTEGER, time TIMESTAMP default (datetime('now', 'localtime')))");

		db.execSQL("DROP TABLE IF EXISTS log_method");
		db.execSQL("CREATE TABLE log_method ("
				+ "_id INTEGER primary key autoincrement, method_id INTEGER, "
				+ "log_id INTEGER, time TIMESTAMP default (datetime('now', 'localtime')))");

		db.execSQL("DROP TABLE IF EXISTS log_category");
		db.execSQL("CREATE TABLE log_category ("
				+ "_id INTEGER primary key autoincrement, category_id INTEGER, "
				+ "log_id INTEGER, time TIMESTAMP default (datetime('now', 'localtime')))");

		db.execSQL("DROP TABLE IF EXISTS saying");
		db.execSQL("CREATE TABLE saying ("
				+ "_id INTEGER primary key autoincrement, "
				+ "author TEXT, content TEXT" + ")");
		db.execSQL("INSERT INTO saying (author, content)" + "VALUES(\"约翰米尔顿\", "
				+ "\"一个人如果能够控制自己激情、欲望和恐惧，那他就胜过国\")");
		db.execSQL("INSERT INTO saying (author, content)"
				+ "VALUES(\"松下幸之助\", " + "\"并不是每一种灾难都是祸，早临的逆境往往是福\")");
		db.execSQL("INSERT INTO saying (author, content)" + "VALUES(\"赫胥黎\", "
				+ "\"充满着欢乐与斗争精神的人们，永远带着欢乐，欢迎雷霆与阳光\")");
		db.execSQL("INSERT INTO saying (author, content)" + "VALUES(\"卢森堡\", "
				+ "\"不管怎样的事情，都请安静地愉快吧！这是人生。我们要依样地接受人生，勇敢的、大胆地，而且永远地微笑着\")");
		db.execSQL("INSERT INTO saying (author, content)" + "VALUES(\"富兰克林\", "
				+ "\"心胸开阔：不要为令人不快的区区琐事而心烦意乱，悲观失望。\")");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		onCreate(db);
	}
}
